package com.pc.tas.learning.trace;

import com.pc.tas.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

public class TraceDAO {

	private static SimpleDateFormat dayDateFormat = new SimpleDateFormat(
			"yyyyMMdd");

	public TraceDAO() {
	}

	public int add(Connection conn, Object obj) throws Exception {
		String date = dayDateFormat.format(new Date());
		String tableName = "tas_trace_" + date;

		StringBuffer sql = new StringBuffer();

		sql.append("");
		sql.append("INSERT INTO ");
		sql.append(tableName);
		sql.append(" ( ");
		sql
				.append("PageId,UserAgent,IP,UserId,Createdate,ActionInfo,UserName,Parameters,Referer,Type,Url,Id,payload,CookieId ");
		sql.append(" ) values ( ");
		sql.append("?,?,?,?,?,?,?,?,?,?, ?,?,?,?");
		sql.append(" ) ");
		PreparedStatement pstmt = null;
		Trace bean = (Trace) obj;
		try {
			bean.setId(date + DBUtil.getID(tableName));
			pstmt = conn.prepareStatement(sql.toString());
			int count = 1;
			pstmt.setLong(count++, bean.getPageId());
			pstmt.setString(count++, bean.getUserAgent());
			pstmt.setString(count++, bean.getIP());
			pstmt.setInt(count++, bean.getUserId());
			pstmt.setTimestamp(count++, bean.getCreatedate());
			pstmt.setString(count++, bean.getActionInfo());
			pstmt.setString(count++, bean.getUserName());
			pstmt.setString(count++, bean.getParameters());
			pstmt.setString(count++, bean.getReferer());
			pstmt.setInt(count++, bean.getType());
			pstmt.setString(count++, bean.getUrl());
			pstmt.setString(count++, bean.getId());
			pstmt.setString(count++, bean.getPayload());
			pstmt.setString(count++, bean.getClientId());
			
			try {
				pstmt.execute();
			} catch (Exception e) {
				e.printStackTrace();
				String createTableSql = "create table " + tableName
						+ " like tas_trace";
				Statement st = conn.createStatement();
				st.execute(createTableSql);

				pstmt.execute();
			}

		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		return 0;
	}

	public void delete(Connection conn, int id) throws Exception {
		String sql = "delete from tas_trace where Id = ? ";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.execute();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}

	}

	public void update(Connection conn, Object obj,String id) throws Exception {
		String tableName = "tas_trace_" + id.substring(0, 8);
		StringBuffer sql = new StringBuffer();
		sql.append("");
		sql.append("update ");
		sql.append(tableName);
		sql.append(" set ");
		sql.append(" id=?,");
		sql.append(" pageId=?,");
		sql.append(" UserAgent=?,");
		sql.append(" IP=?,");
		sql.append(" UserId=?,");
		sql.append(" Createdate=?,");
		sql.append(" ActionInfo=?,");
		sql.append(" UserName=?,");
		sql.append(" Parameters=?,");
		sql.append(" Referer=?,");
		sql.append(" Type=?,");
		sql.append(" Url=?, ");
		sql.append(" CookieId=?, ");
		sql.append(" SessionId=?, ");
		sql.append(" payload=? ");
		sql.append("where Id = ? ");
		PreparedStatement pstmt = null;
		Trace bean = (Trace) obj;
		try {
			pstmt = conn.prepareStatement(sql.toString());
			int count = 1;
			pstmt.setString(count++, bean.getId());
			pstmt.setLong(count++, bean.getPageId());
			pstmt.setString(count++, bean.getUserAgent());
			pstmt.setString(count++, bean.getIP());
			pstmt.setInt(count++, bean.getUserId());
			pstmt.setTimestamp(count++, bean.getCreatedate());
			pstmt.setString(count++, bean.getActionInfo());
			pstmt.setString(count++, bean.getUserName());
			pstmt.setString(count++, bean.getParameters());
			pstmt.setString(count++, bean.getReferer());
			pstmt.setInt(count++, bean.getType());
			pstmt.setString(count++, bean.getUrl());
			pstmt.setString(count++, bean.getClientId());
			pstmt.setString(count++, bean.getPayload());
			pstmt.setString(count++, id);
			pstmt.execute();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
	}
	

	public Object getByResultSet(ResultSet rs) throws Exception {
		Trace bean = new Trace();
		bean.setId(rs.getString("Id"));
		bean.setPageId(rs.getLong("PageId"));
		bean.setUserAgent(rs.getString("UserAgent"));
		bean.setIP(rs.getString("IP"));
		bean.setUserId(rs.getInt("UserId"));
		bean.setCreatedate(rs.getTimestamp("Createdate"));
		bean.setActionInfo(rs.getString("ActionInfo"));
		bean.setUserName(rs.getString("UserName"));
		bean.setParameters(rs.getString("Parameters"));
		bean.setReferer(rs.getString("Referer"));
		bean.setType(rs.getInt("Type"));
		bean.setUrl(rs.getString("Url"));
		bean.setClientId(rs.getString("CookieId"));
		bean.setPayload(rs.getString("payload"));
		return bean;
	}
	
	public Object getById(Connection conn, String id) throws Exception {
		String tableName = "tas_trace_" + id.substring(0, 8);
		String sql = "select * from " + tableName + " where Id=? ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Trace bean = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				bean = (Trace) this.getByResultSet(rs);
			}
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
		}
		return bean;
	}

	public Collection<Trace> getPage(Connection conn, Date date, int start,
			int number) throws Exception {
		
		String dateString = this.dayDateFormat.format(date);
		return this.getPage(conn, dateString, start, number);
	}
	
	public Collection<Trace> getPage(Connection conn, String date, int start,
			int number) throws Exception {
		
		String tableName = "tas_trace_" + date;
		String sql = "select * from " + tableName + " limit ?,?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Collection<Trace> traces = new ArrayList<Trace>();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, number);
			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Trace bean = (Trace) this.getByResultSet(rs);
				traces.add(bean);
			}
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
		}
		return traces;
	}

}
